/**
 * 
 */
package xgenerator.jdbc;

import xgenerator.constants.DbTypes;

/**
 * 类说明：
 * @author liss
 * @version 1.0
 * 
 */
public class JdbcUtils {
	
	/**
	 * 静态代码块，加载并注册数据库驱动——只做一次
	 */
	/*
	static {
		try {
			String[] driverClassNames = { "oracle.jdbc.driver.OracleDriver",
					"com.microsoft.jdbc.sqlserver.SQLServerDriver",
					"com.mysql.jdbc.Driver",
					"sun.jdbc.odbc.JdbcOdbcDriver" };
			for(String oneDriverClassName : driverClassNames) {
				Class.forName(oneDriverClassName);
				System.out.println("加载并注册数据库驱动：" + oneDriverClassName);
			}
			
			
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError("通过字符串名加载Driver类时无法定位该类");
		}
	}
	*/

	/**
	 * <p>
	 * Title：根据数据库JDBC驱动类名，分析数据库类型
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param driverClassName
	 * @return
	 */
	public static final String analysisDbType(String driverClassName) {
		String dbType = null;
		if(-1 != driverClassName.indexOf("oracle")) {
			dbType = DbTypes.ORACLE_TYPE;
		} else if(-1 != driverClassName.indexOf("db2")) {
			dbType = DbTypes.DB2_TYPE;
		} else if(-1 != driverClassName.indexOf("sybase")) {
			dbType = DbTypes.SYBASE_TYPE;
		} else if(-1 != driverClassName.indexOf("sqlserver.jdbc")) {
			dbType = DbTypes.SQLSERVER2000_TYPE;
		} else if(-1 != driverClassName.indexOf("jdbc.sqlserver")) {
			dbType = DbTypes.SQLSERVER2005_TYPE;
		} else if(-1 != driverClassName.indexOf("mysql")) {
			dbType = DbTypes.MYSQL_TYPE;
		} else if(-1 != driverClassName.indexOf("informix")) {
			dbType = DbTypes.INFORMIX_TYPE;
		} else if(-1 != driverClassName.indexOf("postgresql")) {
			dbType = DbTypes.POSTGRESQL_TYPE;
		} else if(-1 != driverClassName.indexOf("derby")) {
			dbType = DbTypes.DERBY_TYPE;
		} else if(-1 != driverClassName.indexOf("sqlite")) {
			dbType = DbTypes.SQLITE_TYPE;
		}
		
		return dbType;
	}
	
	/**
	 * <p>
	 * Title：根据数据库类型获取数据库DriverClassName
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dbType
	 * @return
	 */
	public static String getDriverClassNameByDbType(String dbType) {
		
		String[] driverClassNames = { "oracle.jdbc.driver.OracleDriver",
				"com.sysbase.jdbc.SybDriver",
				"com.microsoft.jdbc.sqlserver.SQLServerDriver",  //2000
				"com.microsoft.sqlserver.jdbc.SQLServerDriver",  //2005
				"com.ibm.db2.jdbc.app.DB2Driver",
				"com.informix.jdbc.IfxDriver",
				"org.postgresql.Driver",
				"com.mysql.jdbc.Driver",
				"sun.jdbc.odbc.JdbcOdbcDriver" };
		
		if("Oracle".equalsIgnoreCase(dbType)) {
			return driverClassNames[0];
		} else if("Sybase".equalsIgnoreCase(dbType)) {
			return driverClassNames[1];
		} else if("SQLServer2000".equalsIgnoreCase(dbType)) {
			return driverClassNames[2];
		} else if("SQLServer2005".equalsIgnoreCase(dbType)) {
			return driverClassNames[3];
		} else if("DB2".equalsIgnoreCase(dbType)) {
			return driverClassNames[4];
		} else if("Informix".equalsIgnoreCase(dbType)) {
			return driverClassNames[5];
		} else if("PostgreSQL".equalsIgnoreCase(dbType)) {
			return driverClassNames[6];
		} else if("MySQL".equalsIgnoreCase(dbType)) {
			return driverClassNames[7];
		}
//		else if("JDBC-ODBC".equalsIgnoreCase(dbType) 
//				|| "Access(*.mdb)".equalsIgnoreCase(dbType) 
//				|| "Excel(*.xls)".equalsIgnoreCase(dbType) 
//				|| "SQLServer(*.mdf)".equalsIgnoreCase(dbType)) {
//			return driverClassNames[8];
//		}
		
		return driverClassNames[8];  //JDBC-ODBC类型与平面数据库
	}
	
	
	/**
	 * 根据数据库类型获取数据库URL
	 * @param dbType
	 * @return
	 */
	public static String getUrlByDbType(String dbType, String dbIp, String dbPort, String dbName) {
		String url = "";
		
		//------------------------------------------------------
		if("Oracle".equalsIgnoreCase(dbType)) {
			url = "jdbc:oracle:thin:@" + dbIp + ":" + dbPort + ":" + dbName;
		}
		//------------------------------------------------------
		else if("Sysbase".equalsIgnoreCase(dbType)) {
			url = "jdbc:Sysbase.Tds." + dbIp + ":" + dbPort + "/" + dbName;
		} else if("SQLServer2000".equalsIgnoreCase(dbType)) {
			url = "jdbc:microsoft:sqlserver://" + dbIp + ":" + dbPort + ";DatabaseName=" + dbName + ";SelectMethod=cursor";
		} else if("SQLServer2005".equalsIgnoreCase(dbType)) {
			url = "jdbc:sqlserver://" + dbIp + ":" + dbPort + ";DatabaseName=" + dbName;
		} else if("DB2".equalsIgnoreCase(dbType)) {
			url = "jdbc:db2://" + dbIp + ":" + dbPort + "/" + dbName;
		} else if("Informix".equalsIgnoreCase(dbType)) {
			url = "jdbc:Informix-sqli://" + dbIp + ":" + dbPort + "/" + dbName;
		} else if("PostgreSQL".equalsIgnoreCase(dbType)) {
			url = "jdbc:postgresql://" + dbIp + ":" + dbPort + "/" + dbName;
		} else if("MySQL".equalsIgnoreCase(dbType)) {
			url = "jdbc:mysql://" + dbIp + ":" + dbPort + "/" + dbName + "?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8";
		}
		//------------------------------------------------------
		else if("JDBC-ODBC".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:" + dbName;
		}
		//------------------------------------------------------
		else if("SQLServer(*.mdf)".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:Driver={MicroSoft dBase Driver (*.mdf)};DBQ=" + dbName;
		} else if("Access(*.mdb)".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=" + dbName;
		} else if("Excel(*.xls)".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:Driver={MicroSoft Excel Driver (*.xls)};DBQ=" + dbName;
		} else if("dBase(*.dbf)".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:Driver={MicroSoft dBase Driver (*.dbf)};DBQ=" + dbName;
		} else if("FoxPro(*.dbf)".equalsIgnoreCase(dbType)) {
			url = "jdbc:odbc:Driver={MicroSoft VFP Driver (*.dbf)};DBQ=" + dbName;
		} else if("Text(*.txt,*.csv)".equalsIgnoreCase(dbType)) {
			//DBQ=D:\\work\\eclipseWorkspace\\JGenerator\\src\\liss\\jgenerator\\resources\\database.mdb";
			url = "jdbc:odbc:Driver={MicroSoft Access Text Driver (*.txt,*.csv)};DBQ=" + dbName;
		}
		
		return url;
	}
	
	/**
	 * 功能：获取Oracle分页方言语句
	 * @author liss
	 * 
	 * @param coreSql
	 * @return
	 */
	public static String getOracleLimitSQL(final String coreSql) {
		StringBuffer pagingSelect = new StringBuffer(100);
		pagingSelect.append("select * from ( select row_.*, rownum as rownum_ from ( ");
		pagingSelect.append(coreSql);
		pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
		return pagingSelect.toString();
	}
	
	public static String getOracleLimitSQL(final String coreSql, int topNo, int endNo) {
		StringBuffer pagingSelect = new StringBuffer(100);
		pagingSelect.append("select * from ( select row_.*, rownum as rownum_ from ( ");
		pagingSelect.append(coreSql);
		pagingSelect.append(" ) row_ where rownum <= " + endNo + ") where rownum_ > " + topNo);
		return pagingSelect.toString();
	}

	
	/**
	 * 功能：获取MySQL分页方言语句
	 * @author liss
	 * 
	 * @param coreSql
	 * @return
	 */
	public String getMySqlLimitSQL(final String coreSql) {
		StringBuffer pagingSelect = new StringBuffer(100);
		pagingSelect.append(coreSql);
		pagingSelect.append(" limit ?, ?");
		return pagingSelect.toString();
	}

	
	
	
	/*
	public List pagingQueryForGenericList_Oracle(final String coreSql,
			final int start, final int end) throws SQLException {
		List<List<String>> resultList = null;

		StringBuffer sb = new StringBuffer(100);
		sb.append("Select ");
		sb.append("  * ");
		sb.append("from ");
		sb.append("( ");
		sb.append("  Select ");
		sb.append("    A,*, ");
		sb.append("    rownum as newRowNum ");
		sb.append("  from ");
		sb.append("  ( ");
		sb.append(coreSql);
		sb.append("  ) A ");
		sb.append("  where ");
		sb.append("    rownum<=" + end);
		sb.append(") ");
		sb.append("where ");
		sb.append("  newRowNum>" + start);

		String sql = sb.toString();
		try {
			resultList = this.queryForGenericList(sql);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw new SQLException("分页查询出现异常");
		}

		return resultList;
	}


	public List pagingQueryForGenericList_MySQL(final String coreSql,
			final int start, final int length) throws SQLException {
		List<List<String>> resultList = null;

		StringBuffer sb = new StringBuffer(100);
		sb.append(coreSql + " limit " + start + "," + length);
		String sql = sb.toString();
		try {
			resultList = this.queryForGenericList(sql);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw new SQLException("分页查询出现异常");
		}

		return resultList;
	}
	*/
	
	
	
	
}
